package pacificlms.Verifiable;

import java.lang.reflect.*;
import java.lang.reflect.AccessibleObject.*;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import javax.swing.JOptionPane;
import java.util.ArrayList;
import java.sql.*;
import pacificlms.MasterConnection;
import pacificlms.Error.ErrorHandle;

/**
 * VerifiableObject acts as a buffer layer between the MYSQL and the code, all of the objects that are exchanged with the database extend the VerifiableObject class.
 * VerifiableObject supplied methods to check that the objects match the database specification, to create SQL commands for insertion and updating, and to create or replace VerifiableObject children with rows from the database.
 * VerifiableObject also supplies methods to get related objects from the database
 * @author Brian Hume
 */
public abstract class VerifiableObject
{
    private boolean Verfied = false;
    
    //Checks to make sure date values actualy make sense(fixme spelling)
    private boolean CheckDate(String Str)
    {
        //fixme throw exeption if date is bad ie 13th month
        return true;
    }
    
    //Checks to make sure numeric values actualy make sense(fixme spelling)
    private boolean CheckNumber(String Str)
    {
        try
        {
            double d = Double.valueOf(Str);
            if (d == Double.NEGATIVE_INFINITY || d == Double.POSITIVE_INFINITY || d == Double.NaN)
            {
                return false;
            }
            return true;
        }
        catch(Exception Ex)
        {
            return false;
        }
    }
    
    //converts string values to strings that can be added to SQL statments
    //removes all MYSQL sensitive characters (", ', %, etc.)
    public static String ConvertToMYSQLString(String Str)
    {
        String CleanString = Str;
        if (Str == null || Str.equals(""))
            return "null";
        
        CleanString = CleanString.replace("\\", "\\\\");
        CleanString = CleanString.replace("\"", "\\\"");
        CleanString = CleanString.replace("'", "\\'");
        CleanString = CleanString.replace("%", "\\%");
        CleanString = CleanString.replace("_", "\\_");
        //fixme more???
        CleanString = "'" + CleanString + "'";
        
        return CleanString;
    }
    
    //retrieves the methods in a class that lookup values from a specific table
    private ArrayList<Method> GetLookupsMethods(Class FromClass, String TargetTableName)
    {
        ArrayList<Method> KeyList = new ArrayList();
        Method[] MethodList = FromClass.getMethods();
        for (int mitr= 0; mitr < MethodList.length; mitr++)
        {
            Method MC = MethodList[mitr];
            VerifyAnnotation Anno = MC.getAnnotation(VerifyAnnotation.class);

            if (Anno != null)
            {
                String LookupTable = Anno.LookupDBTable();
                if (LookupTable != null && !LookupTable.isEmpty())
                {
                    if (LookupTable.equalsIgnoreCase(TargetTableName))
                    {
                        KeyList.add(MC);
                    }
                }
            }
        }
        
        return KeyList;
    }
    
    //Finds the method that a specified annotation belongs to
    private Method GetRelated(VerifiableObject VO, VerifyAnnotation TargAnno)
    {
        Method[] MethodList = this.getClass().getMethods();
        for(Method MC: MethodList)
        {
            VerifyAnnotation ThisAnno = MC.getAnnotation(VerifyAnnotation.class);
            if (ThisAnno != null)
            {
                if (ThisAnno.DBName().equalsIgnoreCase(TargAnno.LookupDBField()))
                    return MC;
            }
        }
        return null;
    }
    
    //Deals with cases where SQL format can contain values that the java format cannot contain (eg the date 0000-00-00)
    private String GetValue(ResultSet RS, ResultSetMetaData Meta, int ColumnIndex)
    {
        try
        {
            Object Val = RS.getObject(ColumnIndex);
            if (Val == null)
                return null;
            else
                return Val.toString();
        }
        catch(Exception Ex)//fixme catches zero dates which bug java may still have issues
        {
            return "1900-01-01";
        }
    }
    
    /**
     * Unverify marks he object as not verified, used by children to deal with values that do not use StandardProcess to convert
     */
    protected final void Unverify()
    {
        Verfied = false;
    }
    
    /**
     * StandardProcess is used to allow the passing of null to not change the return value, but to simply retrieve it
     * StandardProcess also unverifys the object when the value is changed
     * @param Target is the new value to assign to the value
     * @param value value is the previous value
     * @return value if Target is null otherwise Target
     */
    protected final String StandardProcess(String Target, String value)
    {
        if (value != null)
        {
            Target = value;
            Unverify();
        }
        return Target;
    }
    
    //Converts a variable to SQL string information
    protected String SQLVariable(VerifyAnnotation Anno, Method MC) throws Exception
    {
        if (Anno.Type() == TypeEnum.REALNUMBER)
            return (String)MC.invoke(this, (String)null);
        else
            return ConvertToMYSQLString((String)MC.invoke(this, (String)null));
    }
    
    /**
     * TableName returns The table name that this object references
     * @return The table name that this object references
     */
    public abstract String TableName();
    
    /**
     * Verifies that the child class matches specification, displays a message box telling the user how to fix any mistakes that they made
     * Verify will run even if the value of Verfied is true
     * @return true if all of the fields in the child class that are annotated to refer to database fields match the annotated specification
     */
    public final boolean Verify()
    {
        try
        {
            Verfied = false;
            
            Class ThisClass = this.getClass();
            Method[] MethodList = ThisClass.getMethods();
            for (Method MC: MethodList)
            {
                VerifyAnnotation Anno = MC.getAnnotation(VerifyAnnotation.class);
                
                if (Anno != null)
                {
                    int MaxLength = Anno.Length();
                    TypeEnum VariableType = Anno.Type();
                    
                    String Str;
                    if (MC.getReturnType() == String.class)
                        Str = (String)MC.invoke(this, (String)null);
                    else
                    {
                        throw(new VerificationException("Error\n" + Anno.ApplicationName() + " is not a string."));
                    }
                    
                    //NULL
                    if (!Anno.NotNull())
                    {
                        if ((Str == null || Str.length() == 0))
                            continue;//this item allows nulls and is null so its ok
                    }
                    else if ((Str == null || Str.length() == 0))
                    {
                        throw(new VerificationException("Error\n" + Anno.ApplicationName() + " cannot be empty."));
                    }
                    //LENGTH
                    if (MaxLength < Str.length() || Anno.ExactLength() && MaxLength != Str.length())
                    {
                        if (Anno.ExactLength())
                            throw(new VerificationException("Error\n" + Anno.ApplicationName() + "'" + Str + "' must be exactly " + MaxLength + " characters long."));
                        else
                            throw(new VerificationException("Error\n" + Anno.ApplicationName() + "'" + Str + "' must not exceed " + MaxLength + " characters long."));
                    }
                    //FORMAT
                    if (VariableType == TypeEnum.STRING)
                    {
                        //How to fail here??
                    }
                    else if (VariableType == TypeEnum.LOOKUP)
                    {
                        if (Anno.LookupDBTable().equals("") || Anno.LookupDBField().equals(""))
                        {
                            throw(new VerificationException("Error\n" + Anno.ApplicationName() + "'" + Str + "' lacks referencial information."));
                        }
                            
                    }
                    else if (VariableType == TypeEnum.HEXNUMBER)
                    {
                        if (!Str.matches("[0-9A-Fa-f]+"))
                        {
                            throw(new VerificationException("Error\n" + Anno.ApplicationName() + "'" + Str + "' can only contain 0-9 and A-F."));
                        }
                    }
                    else if (VariableType == TypeEnum.NUMERICSTRING)
                    {
                        if (!Str.matches("[0-9]+"))
                        {
                            throw(new VerificationException("Error\n" + Anno.ApplicationName() + "'" + Str + "' can only contain 0-9."));
                        }
                    }
                    else if (VariableType == TypeEnum.REALNUMBER)
                    {
                        if (!CheckNumber(Str))
                        {
                            throw(new VerificationException("Error\n" + Anno.ApplicationName() + "'" + Str + "' must be a number."));
                        }
                    }
                    else if (VariableType == TypeEnum.PHONE)
                    {
                        if (!Str.matches("[0-9\\x28\\x29\\x2D\\x20]+"))
                        {
                            throw(new VerificationException("Error\n" + Anno.ApplicationName() + "'" + Str + "' does not appere to be a phone number eg (604)555-4321 or 1800-555-6789."));
                        }
                    }
                    else if (VariableType == TypeEnum.DATE)
                    {
                        if (!Str.matches("[0-9]{4}\\x2D[0-9]{2}\\x2D[0-9]{2}"))//Match 2011-01-01
                        {
                            throw(new VerificationException("Error\n" + Anno.ApplicationName() + "'" + Str + "' must be in the format YYYY-MM-DD."));
                        }
                        if (!CheckDate(Str))
                        {
                            throw(new VerificationException("Error\n" + Anno.ApplicationName() + "'" + Str + "' must be a date matching YYYY-MM-DD."));
                        }
                    }
                    else if (VariableType == TypeEnum.ENUM)
                    {
                        String EnumValues = "";
                        boolean Match = false;
                        
                        for(String EnumValue: Anno.EnumList())
                        {
                            if (Str.equalsIgnoreCase(EnumValue))
                            {
                                Match = true;
                                break;
                            }
                            EnumValues += ", " + EnumValue;
                        }
                        
                        if (!Match)
                        {
                            throw(new VerificationException("Error\n" + Anno.ApplicationName() + " value '"+Str+"' not match the any of the enumeration list values" + EnumValues + "."));
                        }
                    }
                    else
                    {
                        throw(new VerificationException("Error\n" + Anno.ApplicationName() + "'" + Str + "' has unhandled type " + Anno.Type().toString() + "."));
                    }
                    if (!ExtraVerification())
                    {
                        //do not throw ExtraVerification already threw
                        return false;
                    }
                }
            }
        }
        catch(Exception Ex)
        {
            JOptionPane.showMessageDialog(null, Ex.getMessage(), "Input Error", JOptionPane.ERROR_MESSAGE);
            System.out.println(Ex.toString());
            return false;
        }
        Verfied = true;
        return true;
    }
    
    /**
     * This function provides an interface for child classes to add special verfication
     */
    protected boolean ExtraVerification()
    {
        return true;
    }

    //fixme comment
    public final boolean GetVerifiedStatus()
    {
        return Verfied;
    }
    
    /**
     * Creates a string to insert the class into the database
     * @return A string that can be sent to the MYSQL database to be executed to insert the values from the class into the database
     */
    public final String InsertRecrodSQL()
    {
        try
        {
            if (!Verfied)
                throw(new VerificationException("Not properly verified"));
            String SQL = "INSERT INTO " + TableName();

            String Names = "(";
            String Values = " VALUES (";
            
            Class ThisClass = this.getClass();
            Method[] MethodList = ThisClass.getMethods();
            for(Method MC: MethodList)
            {
                VerifyAnnotation Anno = MC.getAnnotation(VerifyAnnotation.class);
                
                if (Anno != null)
                {
                    String Value = SQLVariable(Anno, MC);

                    Names += Anno.DBName() + ", ";
                    Values += Value + ", ";
                }
            }
            Names = Names.substring(0, Names.length()-2) + ")";
            Values = Values.substring(0, Values.length()-2) + ")";
            
            SQL = SQL + Names + Values;
            
            return SQL;
        }
        catch(Exception Ex)
        {
            JOptionPane.showMessageDialog(null, Ex.getMessage(), "Verification Error", JOptionPane.ERROR_MESSAGE);
            return "";
        }
    }
    
    /**
     * Creates a string to update the class into the database
     * @return A string that can be sent to the MYSQL database to be executed to update the values from the class inside the database
     */
    public final String UpdateRecrodSQL()
    {
        try
        {
            if (!Verfied)
                throw(new VerificationException("Not properly verified"));
            String SQL = "UPDATE " + TableName() + " SET ";

            String SetValues = "";
            String Where = " WHERE ";
            
            Class ThisClass = this.getClass();
            Method[] MethodList = ThisClass.getMethods();
            for(Method MC: MethodList)
            {
                VerifyAnnotation Anno = MC.getAnnotation(VerifyAnnotation.class);
                
                if (Anno != null)
                {
                    String Value = SQLVariable(Anno, MC);

                    if (Anno.PrimaryKey())
                    {
                        Where += Anno.DBName()+"=";
                        Where += Value + " AND ";
                    }
                    else
                    {
                        SetValues += Anno.DBName()+"=";
                        SetValues += Value + ", ";
                    }
                }
            }
            Where = Where.substring(0, Where.length()-4);
            SetValues = SetValues.substring(0, SetValues.length()-2);
            
            SQL = SQL + SetValues + Where;
            
            return SQL;
        }
        catch(Exception Ex)
        {
            JOptionPane.showMessageDialog(null, Ex.getMessage(), "Verification Error", JOptionPane.ERROR_MESSAGE);
            return "";
        }
    }
    
    /**
     * Creates an SQL string to find similar records, ignores nulls and assumes values are substrings
     * @return The SQL string that will find similar data of the same class
     */
    public final String FindSimilarSQL()
    {
        try
        {
            String SQL = "SELECT * FROM " + TableName();

            String Where = " WHERE ";
            
            int WhereCount = 0;
            Class ThisClass = this.getClass();
            Method[] MethodList = ThisClass.getMethods();
            for(Method MC: MethodList)
            {
                VerifyAnnotation Anno = MC.getAnnotation(VerifyAnnotation.class);
                
                if (Anno != null)
                {
                    //why block on primary key?
                    //if (!Anno.PrimaryKey())
                    {
                        String Value = (String)MC.invoke(this, (String)null);
                        if (Value != null && !Value.trim().equals(""))
                        {
                            Where += Anno.DBName() + " LIKE ";
                            Where += "'%" + Value + "%' AND ";
                            WhereCount++;
                        }
                    }
                }
            }
            Where = Where.substring(0, Where.length()-4);
            if (WhereCount == 0)//if there are no where clauses set it to empty
                Where = "";
            SQL = SQL + Where;
            
            return SQL;
        }
        catch(Exception Ex)
        {
            System.out.println(Ex.toString());
            return "";
        }
    }

    
    /**
     * Creates an SQL string to find identical records, ignores nulls and
     * @return The SQL string that will find similar data of the same class
     */
    public final String FindIdenticalSQL()
    {
        try
        {
            String SQL = "SELECT * FROM " + TableName();

            String Where = " WHERE ";
            
            int WhereCount = 0;
            Class ThisClass = this.getClass();
            Method[] MethodList = ThisClass.getMethods();
            for(Method MC: MethodList)
            {
                VerifyAnnotation Anno = MC.getAnnotation(VerifyAnnotation.class);
                
                if (Anno != null)
                {
                    //why block on primary key?
                    //if (!Anno.PrimaryKey())
                    {
                        String Value = (String)MC.invoke(this, (String)null);
                        if (Value != null && !Value.trim().equals(""))
                        {
                            Where += Anno.DBName() + " = ";
                            Where += SQLVariable(Anno, MC) + " AND ";
                            WhereCount++;
                        }
                    }
                }
            }
            Where = Where.substring(0, Where.length()-4);
            if (WhereCount == 0)//if there are no where clauses set it to empty
                Where = "";
            SQL = SQL + Where;
            
            return SQL;
        }
        catch(Exception Ex)
        {
            System.out.println(Ex.toString());
            return "";
        }
    }

    
    /**
     * Fills an instance of the calling class from the current row of the result set
     * Note this does NOT leave the object in an unspecified state
     * @param RS The RecordSet to retrieve a row from
     * @param Meta The RecordSet's meta data
     * @return true if it successfully filled it or false otherwise
     */
    //fixme remove this
    public final boolean FillFromResultSet(ResultSet RS, ResultSetMetaData Meta)//WARNING ONLY TAKES CURRENT RESULT
    {
        try
        {
            Class ThisClass = this.getClass();
            Method[] MethodList = ThisClass.getMethods();
            for(Method MC: MethodList)
            {
                VerifyAnnotation Anno = MC.getAnnotation(VerifyAnnotation.class);
                
                if (Anno != null)
                {
                    String AnnoName = Anno.DBName();
                    
                    int ColumnIndex = RS.findColumn(AnnoName);

                    if (ColumnIndex >= 0)
                    {
                        String SQLValue = GetValue(RS, Meta, ColumnIndex);
                        MC.invoke(this, (String)SQLValue);
                    }
                }
            }
            
            Verfied = true;//its clean since its fresh out of the db
            return true;
        }
        catch(Exception Ex)
        {
            //fixme messagebox
            System.out.println(Ex.toString());
            return false;
        }
    }
    
    /**
     * creates a new instance of the desired class from the current row of the result set
     * @param NewClass is the class the new instance should be
     * @param RS The RecordSet to retrieve a row from
     * @param Meta The RecordSet's meta data
     * @return The table name that this object references
     */
    public final static VerifiableObject CreateFromResultSet(Class NewClass, ResultSet RS, ResultSetMetaData Meta)//WARNING ONLY TAKES CURRENT RESULT
    {
        try
        {
            //Create Instance of desired class
            VerifiableObject NewInstance = (VerifiableObject)ReflectionHelper.CreateEmptyInstance(NewClass);
            if (NewInstance == null)
            {
                throw (new Exception("Error no parameter less constructor exists for " + NewClass.toString()));
            }
            
            //take attributes for class from record set
            Method[] MethodList = NewClass.getMethods();
            for(Method MC: MethodList)
            {
                VerifyAnnotation Anno = MC.getAnnotation(VerifyAnnotation.class);
                
                if (Anno != null)
                {
                    String AnnoName = Anno.DBName();
                    
                    int ColumnIndex = RS.findColumn(AnnoName);

                    if (ColumnIndex >= 0)
                    {
                        String SQLValue = ((VerifiableObject)NewInstance).GetValue(RS, Meta, ColumnIndex);
                        MC.invoke(NewInstance, (String)SQLValue);
                    }
                }
            }
            ((VerifiableObject)NewInstance).Verfied = true;//its clean since its fresh out of the db
            
            return NewInstance;
        }
        catch(Exception Ex)
        {
            ErrorHandle.Display("Database Reading Error", "Unable to retrieve record from database try agian later.\n" + Ex.getMessage(), Ex, true);
            return null;
        }
    }
    
    /**
     * Retrieves an ArrayList of classes related to this in the database, (ex all of the check outs for this member)
     * @param TargetClass the class of objects that we are interested in
     * @return an ArrayList of related objects of the specified class
     */
    public final ArrayList<VerifiableObject> GetRelatedData(Class TargetClass)
    {
        //20 default lookups
        return GetRelatedData(TargetClass, 20);
    }
    /**
     * Retrieves an ArrayList of classes related to this in the database, (ex all of the check outs for this member)
     * @param TargetClass the class of objects that we are interested in
     * @param MaxSize The largest number of records to get
     * @return an ArrayList of related objects of the specified class
     */
    public final ArrayList<VerifiableObject> GetRelatedData(Class TargetClass, int MaxSize)//fixme check how it grabs records
    {
        try
        {
            VerifiableObject TestInstance = (VerifiableObject)ReflectionHelper.CreateEmptyInstance(TargetClass);
            
            //Get list of comparitor fields
            ArrayList<Method> KeyRelated = GetLookupsMethods(TargetClass, this.TableName());
            ArrayList<Method> KeyRelatedReverse = GetLookupsMethods(this.getClass(), TestInstance.TableName());
            KeyRelated.addAll(KeyRelatedReverse);
            if (KeyRelated.isEmpty())
            {
                throw(new Exception("No linked fields between " + this.TableName() + " and " + TestInstance.TableName()));
            }
            
            //Create SQL comparor list
            String SQLComparitor = "";
            for(Method MC: KeyRelated)
            {
                VerifyAnnotation Anno = MC.getAnnotation(VerifyAnnotation.class);
                Method ThisRelated = GetRelated(this, Anno);
                if (ThisRelated == null)
                {
                    throw(new Exception("No linked field is broken for Field(" + Anno.DBName() + ") between " + this.TableName() + " and " + TestInstance.TableName()));
                }
                SQLComparitor += Anno.DBName() + "=" + SQLVariable(Anno, ThisRelated) + " AND ";
            }
            SQLComparitor = SQLComparitor.substring(0, SQLComparitor.length() - 4);
            String SQL = "SELECT * FROM " + TestInstance.TableName() + " WHERE " + SQLComparitor + " LIMIT 0," + Integer.toString(MaxSize);
            
            //Get list of related objects
            ArrayList<VerifiableObject> RelatedArray = new ArrayList<VerifiableObject>();
            Connection Con = MasterConnection.getInstance();
            Statement Stmt = Con.createStatement();
            ResultSet RS = Stmt.executeQuery(SQL);
            ResultSetMetaData Meta = RS.getMetaData();
            while(RS.next())
            {
                VerifiableObject VO = CreateFromResultSet(TargetClass, RS, Meta);
                RelatedArray.add(VO);
            }
            
            return RelatedArray;
        }
        catch(Exception Ex)
        {
            ErrorHandle.Display("Database Reading Error", "Unable to retrieve record from database try agian later.\n" + Ex.getMessage(), Ex, true);
            return null;
        }
    }
    
    public static double GetNumber(String Numb)
    {
        try
        {
            return Double.parseDouble(Numb);
        }
        catch(Exception Ex)
        {
            return 0;
        }
    }
    
    @Override
    public abstract String toString();
}
